﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using uMES.LeanManufacturing.DBUtility;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESAssemblyRecordBusiness
    {
        #region 添加装配拆除记录
        public Boolean AddAssemblyRemoveInfo(Dictionary<string, string> para)
        {
            ArrayList SQLStringList = new ArrayList();

            //新建装配拆除记录
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO assemblyremoveinfo(ID,assemblyrecordinfoid,employeeid,removedate,removereasonid,notes)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["AssemblyRecordInfoID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["EmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["RemoveDate"]));
            strSQL.AppendLine(string.Format("'{0}',", para["RemoveReasonID"]));
            strSQL.AppendLine(string.Format("'{0}'", para["Notes"]));
            strSQL.AppendLine(")");

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //更新装配记录状态
            strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE assemblyrecordinfo SET status = 1 WHERE ID = '{0}'", para["AssemblyRecordInfoID"]));

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 添加装配记录
        public Boolean AddAssemblyRecordInfo(Dictionary<string, string> para)
        {
            string strID = Guid.NewGuid().ToString();
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO assemblyrecordinfo(ID,containerid,specid,childcontainerid,productid,qty,");
            strSQL.AppendLine("     uomid,serialnumber,notes,employeeid,assemblydate,status)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ChildContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ProductID"]));
            strSQL.AppendLine(string.Format("{0},", para["Qty"]));
            strSQL.AppendLine(string.Format("'{0}',", para["UOMID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SerialNumber"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Notes"]));
            strSQL.AppendLine(string.Format("'{0}',", para["EmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["AssemblyDate"]));
            strSQL.AppendLine(string.Format("{0}", para["Status"]));
            strSQL.AppendLine(")");

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 获取装配记录
        public DataTable GetAssemblyList(string strContainerID, string strSpecID, string strProductNoID, string strProductID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT e.fullname,ari.assemblydate,ari.qty,ari.serialnumber,ari.notes,");
            strQuery.AppendLine("   pb.productname,p.description,ari.id");
            strQuery.AppendLine("FROM assemblyrecordinfo ari");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = ari.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = ari.employeeid");
            strQuery.AppendLine("WHERE 1 = 1 AND ari.status = 0");
            strQuery.AppendLine(string.Format("AND ari.containerid = '{0}'", strContainerID));
            strQuery.AppendLine(string.Format("AND ari.specid = '{0}'", strSpecID));
            strQuery.AppendLine(string.Format("AND ari.productid = '{0}'", strProductID));

            if (strProductNoID != string.Empty)
            {
                strQuery.AppendLine(string.Format("AND ari.childcontainerid = '{0}'", strProductNoID));
            }

            strQuery.AppendLine("ORDER BY ari.assemblydate DESC");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取已装配数量
        public DataTable GetAssemblyQty(string strContainerID, string strSpecID, string strProductNoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT ari.productid,SUM(qty) AS qty");
            strQuery.AppendLine("FROM assemblyrecordinfo ari");
            strQuery.AppendLine("WHERE 1 = 1 AND ari.status = 0");
            strQuery.AppendLine(string.Format("AND ari.containerid = '{0}'", strContainerID));
            strQuery.AppendLine(string.Format("AND ari.specid = '{0}'", strSpecID));

            if (strProductNoID != string.Empty)
            {
                strQuery.AppendLine(string.Format("AND ari.childcontainerid = '{0}'", strProductNoID));
            }

            strQuery.AppendLine("GROUP BY ari.productid");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取Product的物料清单
        public DataTable GetMaterialListByProductID(string strProductID, string strSpecID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT productname,DESCRIPTION,productid,productrevision,SUM(qtyrequired) AS qtyrequired");
            strQuery.AppendLine("FROM (");
            strQuery.AppendLine("SELECT NVL(pb.productname, pb1.productname) AS productname,");
            strQuery.AppendLine("   NVL(p.description, p1.description) AS DESCRIPTION,");
            strQuery.AppendLine("   NVL(p.productid,p1.productid) AS productid,");
            strQuery.AppendLine("   NVL(p.productrevision,p1.productrevision) AS productrevision,");
            strQuery.AppendLine("   pmli.qtyrequired");
            strQuery.AppendLine("FROM productmateriallistitem pmli");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = pmli.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN productbase pb1 ON pb1.productbaseid = pmli.productbaseid");
            strQuery.AppendLine("LEFT JOIN product p1 ON p1.productid = pb1.revofrcdid");
            strQuery.AppendLine("WHERE pmli.bomid = (");
            strQuery.AppendLine("   SELECT(CASE WHEN pm.bomid = '0000000000000000'");
            strQuery.AppendLine("       THEN(SELECT bb.revofrcdid FROM bombase bb WHERE bb.bombaseid = pm.bombaseid)");
            strQuery.AppendLine("       ELSE pm.bomid END) AS bomid");
            strQuery.AppendLine("   FROM product pm");
            strQuery.AppendLine(string.Format("   WHERE pm.productid = '{0}'", strProductID));
            strQuery.AppendLine(")");

            if (strSpecID != string.Empty)
            {
                strQuery.AppendLine(string.Format("AND (pmli.specid = '{0}' OR (SELECT revofrcdid FROM specbase WHERE specbaseid = pmli.specbaseid) = '{0}')", strSpecID));
            }

            strQuery.AppendLine(")");
            strQuery.AppendLine("GROUP BY productname,DESCRIPTION,productid,productrevision");

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取批次信息
        public DataTable GetContainerInfo(string strContainerName)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT c.containername,pb.productname,p.description,c.qty,c.plannedstartdate,c.containerid,");
            strQuery.AppendLine("   wfs.workflowid,mo.processno,mo.oprno,c.plannedcompletiondate,c.productid,");
            strQuery.AppendLine("   c.childcount");
            strQuery.AppendLine("FROM container c");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN currentstatus cu ON cu.currentstatusid = c.currentstatusid");
            strQuery.AppendLine("LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid");
            strQuery.AppendLine("WHERE c.status = 1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine(string.Format("AND c.containername = '{0}'", strContainerName));

            DataTable DT = OracleHelper.GetDataTable(strQuery.ToString());
            return DT;
        }
        #endregion
    }
}
